﻿using Minecraft.Config;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Minecraft.Model;
using System.Text.RegularExpressions;

namespace Minecraft.DALMySql
{
	public partial class BaseDAL
	{
		/// <summary>
		/// 分表信息插入
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="model"></param>
		/// <param name="tableNameCacheList">表名称缓存列表</param>
		/// <param name="keyId">主键Id</param>
		/// <param name="tableNamePrefix">表名称前缀</param>
		/// <param name="submeterLenStr">根据keyId分表间隔长度</param>
		/// <param name="createTableSqlFunc">创建表的sql func</param>
		/// <returns></returns>
		protected static bool InsertSuccessModelData<T>(T model,
			List<string> tableNameCacheList,
			int keyId,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc) where T : class
		{
			if (AddTableSuccess(keyId, tableNameCacheList, tableNamePrefix, submeterLen, createTableSqlFunc))
			{
				using (var Conn = GetConn())
				{
					Conn.Open();
					var propKeys = model.GetAllFieldKeys();
					var names = string.Join(",", propKeys.ToArray());
					var values = string.Join(",", propKeys.ToList().ConvertAll(m => "@" + m).ToArray());
					string sql = string.Format(@"insert into {0}({1}) values({2});",
						GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen),
						names,
						values);
					return Conn.Execute(sql, model) > 0;
				}
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 根据前缀删除表
		/// </summary>
		/// <param name="prefixName"></param>
		public static void DropTablesWithPrefix(TablePrefixKey prefixName)
		{
			var list = GetTableNames(prefixName);
			if (list.Any())
			{
				string sql = "";
				list.ForEach(item =>
				{
					sql += $"drop table if exists {item};";
				});
				using (var Conn = GetConn())
				{
					Conn.Open();
					Conn.Execute(sql);
				}
			}
		}

		/// <summary>
		/// 分表获取单个数据
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="keyId"></param>
		/// <param name="tableNamePrefix"></param>
		/// <param name="submeterLenStr"></param>
		/// <param name="keyValue"></param>
		/// <returns></returns>
		protected static T GetSingleOrDefaultWithTablePrefix<T, V>(T model, int keyId,
			List<string> tableNameList,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			KeyValuePair<string, V> keyValue,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc) where T : class
		{
			AddTableSuccess(keyId, tableNameList, tableNamePrefix, submeterLen, createTableSqlFunc);
			using (var Conn = GetConn())
			{
				Conn.Open();
				string sql = $"select * from {GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen)} where {keyValue.Key}={GetTypeValueStr(keyValue.Value)} limit 1;";
				return Conn.QueryFirstOrDefault<T>(sql);
			}
		}

		protected static List<T> GetListAllWithTablePrefix<T, V>(T model, int keyId,
			List<string> tableNameList,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			KeyValuePair<string, V> keyValue,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc) where T : class
		{
			AddTableSuccess(keyId, tableNameList, tableNamePrefix, submeterLen, createTableSqlFunc);
			using (var Conn = GetConn())
			{
				Conn.Open();
				string sql = $"select * from {GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen)} where {keyValue.Key}={GetTypeValueStr(keyValue.Value)};";
				return Conn.Query<T>(sql).ToList();
			}
		}

		protected static List<T> GetListAllWithTablePrefix<T, V, U>(T model, int keyId,
			List<string> tableNameList,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			KeyValuePair<string, V> keyValue1,
			KeyValuePair<string, List<U>> keyValues2,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc
			) where T : class
		{
			AddTableSuccess(keyId, tableNameList, tableNamePrefix, submeterLen, createTableSqlFunc);
			using (var Conn = GetConn())
			{
				Conn.Open();
				string sql = $"select * from {GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen)} where {keyValue1.Key}={GetTypeValueStr(keyValue1.Value)} and {keyValues2.Key}in ({   string.Join(",", keyValues2.Value.ConvertAll(u => { return GetTypeValueStr(u); }).ToArray())   });";
				return Conn.Query<T>(sql).ToList();
			}
		}
		/// <summary>
		/// 批量更新信息(针对分表)（对于数据库操作时，先删除后插入）
		/// </summary>
		/// <param name="isCacheAfterDbOpt">缓存后是否进行数据库操作</param>
		protected static bool BatchUpdateWithTablePrefix<T, V>(List<T> list,
			List<string> tableNameCacheList,
			int keyId,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			KeyValuePair<string, V> keyValue1,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc) where T : class, new()
		{
			if (!list.Any())
			{
				return false;
			}
			if (AddTableSuccess(keyId, tableNameCacheList, tableNamePrefix, submeterLen, createTableSqlFunc))
			{
				string deleteSql = $"delete from {GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen)} where {keyValue1.Key}={GetTypeValueStr(keyValue1.Value)};";
				string batchInsertSql = GetBatchInsertSql(list, GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen));
				string sql = deleteSql + batchInsertSql;
				using (var Conn = GetConn())
				{
					Conn.Open();
					return Conn.Execute(sql) > 0;
				}
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 批量更新信息(针对分表)（对于数据库操作时，先删除后插入）
		/// </summary>
		/// <param name="isCacheAfterDbOpt">缓存后是否进行数据库操作</param>
		protected static bool BatchUpdateWithTablePrefix<T, V, U>(List<T> list,
			List<string> tableNameCacheList,
			int keyId,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			KeyValuePair<string, V> keyValue1,
			KeyValuePair<string, U> keyValue2,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc) where T : class, new()
		{
			if (!list.Any())
			{
				return false;
			}
			if (AddTableSuccess(keyId, tableNameCacheList, tableNamePrefix, submeterLen, createTableSqlFunc))
			{
				string deleteSql = $"delete from {GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen)} where {keyValue1.Key}={GetTypeValueStr(keyValue1.Value)} and {keyValue2.Key}={GetTypeValueStr(keyValue2.Value)};";
				string batchInsertSql = GetBatchInsertSql(list, GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen));
				string sql = deleteSql + batchInsertSql;
				using (var Conn = GetConn())
				{
					Conn.Open();
					bool flag = false;
					if (JsonConfig.Value.IsDebug)
					{
						try
						{
							flag = Conn.Execute(sql) > 0;
						}
						catch (Exception ex)
						{
							string excepStr = $"异常内容：{ex.Message}，可能原因：增删改了分表字段名称，但是没有及时更新【Config项目】中的【TablePrefixSql 文件夹】的内容";
							throw new Exception(excepStr);
						}
					}
					else
					{
						flag = Conn.Execute(sql) > 0;
					}
					return flag;
				}
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 批量更新信息(针对分表)（对于数据库操作时，先删除后插入）
		/// </summary>
		/// <param name="isCacheAfterDbOpt">缓存后是否进行数据库操作</param>
		protected static bool BatchUpdateWithTablePrefix<T, V, U>(List<T> list,
			List<string> tableNameCacheList,
			int keyId,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			KeyValuePair<string, V> keyValue1,
			KeyValuePair<string, List<U>> keyValues,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc) where T : class, new()
		{
			if (!list.Any())
			{
				return false;
			}
			if (AddTableSuccess(keyId, tableNameCacheList, tableNamePrefix, submeterLen, createTableSqlFunc))
			{
				string deleteSql = $"delete from {GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen)} where {keyValue1.Key}={GetTypeValueStr(keyValue1.Value)} and {keyValues.Key} in ({   string.Join(",", keyValues.Value.ConvertAll(u => { return GetTypeValueStr(u); }).ToArray())   });";
				string batchInsertSql = GetBatchInsertSql(list, GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen));
				string sql = deleteSql + batchInsertSql;
				var listStr = list.JsonSerialize();
				using (var Conn = GetConn())
				{
					Conn.Open();
					return Conn.Execute(sql) > 0;
				}
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 判断表是否存在
		/// </summary>
		/// <param name="tableName"></param>
		/// <returns></returns>
		private static bool JudgeTableExists(string tableName)
		{
			string sql = $"show tables like '{tableName}'; ";
			using (var Conn = GetConn())
			{
				Conn.Open();
				return !string.IsNullOrWhiteSpace(Conn.QueryFirstOrDefault<string>(sql));
			}
		}

		private static List<string> GetTableNames(TablePrefixKey prefixName)
		{
			List<string> list = new List<string>();
			string sql = $"show tables like '{prefixName.ToString()}_%'; ";
			using (var Conn = GetConn())
			{
				Conn.Open();
				list = Conn.Query<string>(sql).ToList();
			}
			return list;
		}



		protected static string GetTableNameWithTablePrefix(int keyId, TablePrefixKey tableNamePrefix, int submeterLen)
		{
			string tableName = $"{tableNamePrefix.ToString()}_{CommonConfig.GetTablePostfix(keyId, submeterLen)}";
			return tableName;
		}

		/// <summary>
		/// 动态增加表（分表处理），如果存在，则不做处理
		/// </summary>
		/// <param name="keyId"></param>
		/// <param name="tableNameList">goods表名称内存缓存</param>
		/// <returns></returns>
		private static bool AddTableSuccess(int keyId,
			List<string> tableNameList,
			TablePrefixKey tableNamePrefix,
			int submeterLen,
			Func<int, TablePrefixKey, int, string> createTableSqlFunc)
		{
			string tableName = GetTableNameWithTablePrefix(keyId, tableNamePrefix, submeterLen);
			if (tableNameList.Any(m => m == tableName))
			{
				return true;
			}
			var isTableExists = JudgeTableExists(tableName);
			if (isTableExists)
			{
				tableNameList.Add(tableName);
				return true;
			}
			string sql = createTableSqlFunc(keyId, tableNamePrefix, submeterLen);
			using (var Conn = GetConn())
			{
				Conn.Open();
				Conn.Execute(sql);
				isTableExists = JudgeTableExists(tableName);
				if (isTableExists)
				{
					tableNameList.Add(tableName);
				}
				return isTableExists;
			}
		}
		/// <summary>
		/// 动态建立prefix数据表
		/// </summary>
		/// <param name="playerId"></param>
		/// <returns></returns>
		public static string GetPrefixTableSql(int keyId, TablePrefixKey tablePrefixKey, int submeterLen)
		{
			string tableName = GetTableNameWithTablePrefix(keyId, tablePrefixKey, submeterLen);
			string sql = TablePrefixSqlHelper.GetSqlStr(tablePrefixKey);
			sql = Regex.Replace(sql, @"\b" + tablePrefixKey.ToString() + @"\b", tableName);
			return sql;
		}

	}
}
